using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Configuration;

/// <summary>
/// Summary description for DBHelper
/// </summary>
public class DBHelper
{
    public string _connectionStr = null;
    public DBHelper()
    {
        this._connectionStr = WebConfigurationManager.ConnectionStrings["LoadTestConnectionString"].ConnectionString;
    }

    /// <summary>
    /// Get all LoadTests
    /// </summary>
    public ArrayList GetLoadTests(int daysBefore)
    {
        if (daysBefore < 0)
            return GetLoadTestNames("select distinct LoadTestName from loadtestrun");
        else
            return GetLoadTestNames("select distinct LoadTestName from loadtestrun where DateDiff(day, EndTime, GetDate()) <= " + daysBefore.ToString());
    }

    /// <summary>
    /// Query for first colum
    /// </summary>
    private ArrayList GetLoadTestNames(string sqlCommand)
    {
        ArrayList list = new ArrayList();
        list.Add("All Tests");
        SqlDataReader reader = null;
        SqlConnection connection = new SqlConnection(_connectionStr);

        try
        {
            connection.Open();
            SqlCommand myCommand = new SqlCommand(sqlCommand, connection);
            reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

            while (reader.Read())
            {
                list.Add(reader.GetString(0));
            }
        }
        catch (Exception e)
        {
            Debug.WriteLine(e.ToString());
        }
        finally
        {
            if (reader != null)
                reader.Close();

            if (connection != null)
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
        }
        return list;
    }

    /// <summary>
    /// This will load fields displayed on Edit page.
    /// </summary>
    public void ReadRecord(
        int runId,
        ref string loadTestName,        
        ref string description,
        ref string comment)
    {
        SqlDataReader reader = null;
        SqlConnection connection = new SqlConnection(_connectionStr);

        // get loatTestName, decription, comment
        string sqlCommand = "SELECT LoadTestName, Description, Comment FROM LoadTestRun WHERE LoadTestRunId =" + runId;
        try
        {
            connection.Open();
            SqlCommand myCommand = new SqlCommand(sqlCommand, connection);
            reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

            // only read the first row
            if (reader.Read())
            {
                loadTestName = reader.GetString(0);
                description = reader.GetString(1);
                comment = reader.GetString(2);
            }
        }
        catch (Exception e)
        {
            Debug.WriteLine(e.ToString());
        }
        finally
        {
            if (reader != null)
                reader.Close();

            if (connection != null)
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
        }

    }

    /// <summary>
    /// Update information after edit
    /// </summary>
    public void UpdateRecord(
        int runId,        
        string description,
        string comment)
    {
        SqlConnection connection = new SqlConnection(_connectionStr);

        try
        {
            SqlCommand myCommand = new SqlCommand("Update LoadTestRun set description = @desc, comment=@comment where LoadTestRunId = @LoadTestRunId", connection);
            myCommand.CommandType = CommandType.Text;
            SqlParameter RunId = new SqlParameter("@LoadTestRunId", SqlDbType.Int);
            SqlParameter Description = new SqlParameter("@desc", SqlDbType.NChar, 255);
            SqlParameter Comment = new SqlParameter("@comment", SqlDbType.NText);

            RunId.Value = runId;
            Description.Value = description;
            Comment.Value = comment;

            myCommand.Parameters.Add(RunId);            
            myCommand.Parameters.Add(Description);
            myCommand.Parameters.Add(Comment);

            connection.Open();
            myCommand.ExecuteNonQuery();
        }
        finally
        {
            if (connection != null && connection.State != ConnectionState.Closed)
            {
                connection.Close();
            }
        }
    }

    public void DeleteRuns(List<int> RunIds)
    {
        SqlConnection connection = new SqlConnection(_connectionStr);

        try
        {
            if (RunIds.Count > 0)
            {
                connection.Open();                
            }

            //create the command
            SqlCommand myCommand = new SqlCommand("dbo.[Prc_DeleteLoadTestRun]", connection);
            myCommand.CommandType = CommandType.StoredProcedure;
            
            //up the timeout to have 1 minute per run to delete
            myCommand.CommandTimeout = 600;

            foreach (int id in RunIds)
            {                
                //set the parameter
                SqlParameter param = new SqlParameter("@LoadTestRunId", SqlDbType.Int);
                param.Value = id;
                myCommand.Parameters.Clear();
                myCommand.Parameters.Add(param);

                //execute command                
                myCommand.ExecuteNonQuery();
            }
        }
        finally
        {
            if (connection != null && connection.State != ConnectionState.Closed)
            {
                connection.Close();
            }
        }
    }

    
}
